/**
 * 
 */
package org.gov.vis.master.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import org.gov.vis.master.dto.OtherDto;
import org.gov.vis.util.DBConstants;
import org.gov.vis.util.DBStatements;
import org.gov.vis.util.Util;

/**
 * @author Vishwanath
 *
 */
public class OtherDaoImpl implements OtherDao{

	/**
	 *Method called to either Add or Edit a Other
	 */
	@Override
	public void addOrEditOther(final OtherDto _otherDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			if(_otherDto.getOtherId() > 0){
				pStmt = conn.prepareStatement(DBStatements.EDIT_OTHER);
				pStmt.setString(1, _otherDto.getCode());
				pStmt.setString(2, _otherDto.getNameOfOrg());
				pStmt.setString(3, _otherDto.getNameOfAIWorker());
				pStmt.setString(4, _otherDto.getHeadQuarter());
				pStmt.setString(5, _otherDto.getJuridiction());
				pStmt.setString(6, _otherDto.getMandal());
				pStmt.setString(7, _otherDto.getDistrict());
				pStmt.setLong(8, _otherDto.getOtherId());
			}
			else{
				pStmt = conn.prepareStatement(DBStatements.ADD_OTHER);
				pStmt.setString(1, _otherDto.getCode());
				pStmt.setString(2, _otherDto.getNameOfOrg());
				pStmt.setString(3, _otherDto.getNameOfAIWorker());
				pStmt.setString(4, _otherDto.getHeadQuarter());
				pStmt.setString(5, _otherDto.getJuridiction());
				pStmt.setString(6, _otherDto.getMandal());
				pStmt.setString(7, _otherDto.getDistrict());
			}

			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}
	}//end addOrEditDept

	/**
	 *Method called to Delete a Other
	 */
	@Override
	public void deleteOther(final long _otherId) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.DELETE_OTHER);
			pStmt.setLong(1, _otherId);
			pStmt.executeUpdate();
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

	}//end deleteDept

	/**
	 *Method called to get the list of Others based on the passed Other 
	 */
	@Override
	public List<OtherDto> getOthers(final OtherDto _otherDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<OtherDto> _otherList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_OTHERS);
			if(_otherDto.getOtherId() > 0){
				pStmt.setLong(1, _otherDto.getOtherId());
				pStmt.setLong(2, _otherDto.getOtherId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			
			if(_otherDto.getCode() != null && !_otherDto.getCode().trim().equals("")){
				pStmt.setString(3, _otherDto.getCode());
			}
			else{
				pStmt.setString(3, "%");
			}
			
			rsSet = pStmt.executeQuery();
			
			_otherList = populateOthers(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _otherList;
	}//end getOthers


	/**
	 * Method called to populate the Other records based on the passed result set
	 * @param _rsSet
	 * @return
	 */
	private List<OtherDto> populateOthers(final ResultSet _rsSet)throws Exception{
		List<OtherDto> _otherList = null;
		OtherDto _otherDto = null;
		
		while(_rsSet.next()){
			if(_rsSet.isFirst()){
				_otherList = new ArrayList<OtherDto>();
			}
			//initialize the Other
			_otherDto = new OtherDto();
			_otherDto.setOtherId(_rsSet.getLong(DBConstants.COL_OTHER_ID));
			_otherDto.setCode(_rsSet.getString(DBConstants.COL_OTHER_CODE));
			_otherDto.setNameOfOrg(_rsSet.getString(DBConstants.COL_OTHER_ORGANIZATION));
			_otherDto.setNameOfAIWorker(_rsSet.getString(DBConstants.COL_OTHER_AI_WORKER));
			_otherDto.setHeadQuarter(_rsSet.getString(DBConstants.COL_OTHER_HEADQUARTER));
			_otherDto.setJuridiction(_rsSet.getString(DBConstants.COL_OTHER_JURIDICTION));
			_otherDto.setMandal(_rsSet.getString(DBConstants.COL_OTHER_MANDAL));
			_otherDto.setDistrict(_rsSet.getString(DBConstants.COL_OTHER_DISTRICT));
			//add the current Object to the list
			_otherList.add(_otherDto);
			
		}
		return _otherList;
	}//end populateOthers

	/**
	 * Method is called to search the Others based on the other.
	 */
	@Override
	public List<OtherDto> searchOthers(final OtherDto _otherDto) throws Exception {
		Connection conn = null;
		PreparedStatement pStmt = null;
		ResultSet rsSet = null;
		List<OtherDto> _otherList = null;
		try {
			conn = Util.getConnection();
			pStmt = conn.prepareStatement(DBStatements.SELECT_OTHERS);
			if(_otherDto.getOtherId() > 0){
				pStmt.setLong(1, _otherDto.getOtherId());
				pStmt.setLong(2, _otherDto.getOtherId());
			}
			else{
				pStmt.setLong(1, Long.MIN_VALUE);
				pStmt.setLong(2, Long.MAX_VALUE);
			}
			
			if(_otherDto.getCode() != null && !_otherDto.getCode().trim().equals("")){
				pStmt.setString(3, _otherDto.getCode() + "%");
			}
			else{
				pStmt.setString(3, "%");
			}
			
			rsSet = pStmt.executeQuery();
			
			_otherList = populateOthers(rsSet);
		} catch (Exception e) {
			throw e;
		} finally {
			pStmt.close();
			conn.close();
		}	

		return _otherList;
	}//end searchOthers

}//end class
